package cn.com.wxd.service.attach.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.com.wxd.common.util.JdbcUtil;
import cn.com.wxd.service.attach.entity.AttachInfo;

/**
 * Title:
 * Description:
 * <p>
 * Company: wxd网站系统
 * Copyright: Copyright (c) 2017
 * All right reserved.
 * Created by WangXuDong on 2017/8/16.
 *
 * @author WangXuDong
 * @version 1.0
 */
public class AttachDao {

    private static final Logger log = LoggerFactory.getLogger(AttachDao.class);
    private Connection conn;

    public AttachInfo getAttachInfoById(String id) {
        AttachInfo attachInfo = null;
        if (id == null || id.isEmpty()) {
            log.warn("查询文件记录表信息参数id不能为Null");
            return null;
        }
        conn = JdbcUtil.getConnection();
        String sql = "SELECT * FROM FRAME_ATTACH WHERE fileid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                attachInfo = new AttachInfo();
                attachInfo.setFileId(rs.getString("fileid"));
                attachInfo.setFileName(rs.getString("filename"));
                attachInfo.setUpFileName(rs.getString("upfilename"));
                attachInfo.setSize(rs.getString("size"));
                attachInfo.setExtName(rs.getString("extname"));
                attachInfo.setPath(rs.getString("path"));
                attachInfo.setFileType(rs.getString("filetype"));
                attachInfo.setAccessPath(rs.getString("accesspath"));
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.error("数据查询出现错误" + sql + "，堆栈：", e);
        } finally {
            JdbcUtil.closeConn(conn);
        }
        return attachInfo;
    }

    public List<AttachInfo> getAttachInfosByIds(String[] ids) {
        if (ids == null || ids.length <= 0) {
            log.warn("查询文件记录表信息参数id不能为Null");
            return null;
        }
        List<AttachInfo> attachInfos = new ArrayList<>();
        conn = JdbcUtil.getConnection();
        String instr = "";
        for (int i = 0; i < ids.length; i++) {
            instr += "'" + ids[i] + "',";
        }
        if (instr.equals("")) {
            return null;
        } else {
            instr = instr.substring(0, instr.length() - 2);
        }
        String sql = "SELECT * FROM FRAME_ATTACH where fileid in(" + instr + ")";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                AttachInfo attachInfo = new AttachInfo();
                attachInfo.setFileId(rs.getString("fileid"));
                attachInfo.setFileName(rs.getString("filename"));
                attachInfo.setUpFileName(rs.getString("upfilename"));
                attachInfo.setSize(rs.getString("size"));
                attachInfo.setExtName(rs.getString("extname"));
                attachInfo.setPath(rs.getString("path"));
                attachInfo.setFileType(rs.getString("filetype"));
                attachInfo.setAccessPath(rs.getString("accesspath"));
                attachInfos.add(attachInfo);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.error("数据查询出现错误" + sql + "，堆栈：" + e);
        } finally {
            JdbcUtil.closeConn(conn);
        }
        return attachInfos;
    }

    public void insertAttachInfo(AttachInfo attachInfo) {
        if (attachInfo == null) {
            log.warn("插入attach信息到数据库失败 attachInfo = null");
            return;
        }
        conn = JdbcUtil.getConnection();
        String sql = "INSERT INTO FRAME_ATTACH (fileid,filename,upfilename,size,extname,path,filetype,accesspath) "
                + "values ?,?,?,?,?,?,?,?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, attachInfo.getFileId());
            pstmt.setString(2, attachInfo.getFileName());
            pstmt.setString(3, attachInfo.getUpFileName());
            pstmt.setString(4, attachInfo.getSize());
            pstmt.setString(5, attachInfo.getExtName());
            pstmt.setString(6, attachInfo.getPath());
            pstmt.setString(7, attachInfo.getFileType());
            pstmt.setString(8, attachInfo.getAccessPath());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            log.error("插入数据Frame_attach表数据出现错误,sql："+ sql + "，堆栈：" , e);
        }finally {
            JdbcUtil.closeConn(conn);
        }
    }

    public void deleteAttachInfo(String id) {
        if (id == null || id.isEmpty()) {
            log.warn("删除附件信息错误：id = null or emptyString");
            return;
        }
        conn = JdbcUtil.getConnection();
        String sql = "DELETE FROM FRAME_ATTACH WHERE fileid = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            pstmt.executeUpdate();
            pstmt.close();
        }catch (SQLException e){
            log.error("删除附件信息出现错误",e);
        }finally {
            JdbcUtil.closeConn(conn);
        }
    }

    public AttachInfo getAttachInfoByFileName(String upFileName) {
        AttachInfo attachInfo = null;
        if (upFileName == null || upFileName.isEmpty()) {
            log.warn("查询文件记录表信息参数upFileName不能为Null");
            return null;
        }
        conn = JdbcUtil.getConnection();
        String sql = "SELECT * FROM FRAME_ATTACH WHERE upfilename = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, upFileName);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                attachInfo = new AttachInfo();
                attachInfo.setFileId(rs.getString("fileid"));
                attachInfo.setFileName(rs.getString("filename"));
                attachInfo.setUpFileName(rs.getString("upfilename"));
                attachInfo.setSize(rs.getString("size"));
                attachInfo.setExtName(rs.getString("extname"));
                attachInfo.setPath(rs.getString("path"));
                attachInfo.setFileType(rs.getString("filetype"));
                attachInfo.setAccessPath(rs.getString("accesspath"));
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            log.error("数据查询出现错误" + sql + "，堆栈：", e);
        } finally {
            JdbcUtil.closeConn(conn);
        }
        return attachInfo;
    }
}
